/*
 * BackupItemDAO.java
 *
 * Created on February 18, 2007, 8:31 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package edu.byu.isys413.samlam;

import com.sun.org.apache.xalan.internal.xsltc.compiler.util.NumberType;
import java.sql.*;
import java.util.LinkedList;
import java.util.List;

/**
 * Backup Item
 * @author Sam
 */
public class BackupItemDAO {
    ///////////////////////////////////////
    ///   Singleton pattern
    
    private static BackupItemDAO instance;
    
    /** Creates a new instance of BackupItemDAO */
    private BackupItemDAO() {
    }

    
    
    /**
     * get instance
     * @return get instance
     */
    public static synchronized BackupItemDAO getInstance() {
        if (instance == null) {
            instance = new BackupItemDAO();
        }
        return instance;
    }
    
    /////////////////////////////////
    ///   CREATE
    
    /**
     * There's no need for two creates because we don't need
     * a connection to create BOs.  We run the insert statement
     * later, when it get's saved for the first time.
     * @return create
     */
    public BackupItem create() {
        BackupItem buitem = null;
        try{
            String id = GUID.generate();
            buitem = new BackupItem(id);
            Cache cache = Cache.getInstance();
            cache.put(buitem.getId(), buitem);
            
        }catch(Exception e){
            e.printStackTrace();
        }
        return buitem;
    }
    
    
    /////////////////////////////////////
    ///   READ
    
    /**
     * This is the public read statement.  It loads an existing record
     * from the database.
     * @param id id
     * @throws edu.byu.isys413.samlam.DataException Data Exception
     * @return read
     */
    public synchronized BackupItem read(String id) throws DataException {
        // check to see if id in the cache
        BackupItem buitem = (BackupItem)Cache.getInstance().get(id);
        if(buitem != null){
            // if so, return it immediately
            return buitem;
        }
        
        
        
        Connection conn = null;
        try {
            // retrieve a database connection from the pool
            conn = ConnectionPool.getInstance().get();
            // call read with a connection (the other read method in this class)
            buitem = BackupItemDAO.getInstance().read(id, conn);
            
            // release the connection
            conn.commit();
            ConnectionPool.getInstance().release(conn);
            
        }catch (ConnectionPoolException e4){
            throw new DataException("Could not get a connection to the database.");
            
        }catch (SQLException e) {
            // rollback
            try{
                conn.rollback();
                ConnectionPool.getInstance().release(conn);
            }catch(SQLException e2){
                throw new DataException("Big error: could not even release the connection", e2);
            }catch (ConnectionPoolException e3){
                throw new DataException("Could not get a connection to the database.");
                
            }
            throw new DataException("Could not retrieve record for id=" + id, e);
        }
        
        // return object
        return buitem;
    }
    
    /**
     *  This is a package method that is called by the public read (above) or
     *  by another DAO.  Either way we already have a connection to the database
     *  to use.  The user (controller) code never calls this one directly, since
     *  it can't know about Connection objects or SQLExceptions.
     */
    synchronized BackupItem read(String id, Connection conn) throws SQLException, DataException {
        // check again if the id is in the cache, and if so,
        // just get it from the cache.  we need to check again
        // because this method might be called directly from
        // another DAO rather than from read above.
        BackupItem backupitem = (BackupItem)Cache.getInstance().get(id);
        if(backupitem != null){
            return backupitem;
        }
        // if not in the cache, get a result set from
        // a SELECT * FROM table WHERE id=guid
        Statement stmt = conn.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT * FROM backupitem WHERE id = '"+ id + "'");
        
        //create a prepared statement
        //query the database
        //call sets from the result set
        BackupItem buitem;
        String memberId=null;
        if (rs.next()) {
            buitem = new BackupItem(rs.getString("storageItem_id"));
            
            
            buitem.setFileData(rs.getBinaryStream("fileData"));
            buitem.setFilesize(rs.getInt("filesize"));
            buitem.setMemberid(rs.getString("memberid"));
            buitem.setName(rs.getString("filename"));
            buitem.setStatus(rs.getString("status"));
            buitem.setUploadDate(rs.getString("uploadDate"));
            
            boolean deleted;
            if(rs.getInt("deleted")== 1){
                
                buitem.setDeleted(true);
            }else{
                
                buitem.setDeleted(false);
            }
            
                       
            // get any subobjects (for example, if this was the BackupItemDAO,
            // we'd need to call the MembershipDAO to load the membership record.
            if(buitem.getExtension()==null){
                Extension e = ExtensionDAO.getInstance().read(rs.getString("extension"), conn);
                buitem.setExtension(e);
            }
            
            buitem.setObjectAlreadyInDB(true);
            
            
            // save to the cache
            
            Cache.getInstance().put(buitem.getId(), buitem);
            stmt.close();
        }else{
            throw new DataException("Object was not found in the database.");
            
        }
        // return the BO
        return buitem;
    }
    
    //////////////////////////////////
    ///   UPDATE
    
    /**
     * This is the public save method.  It is what is called when
     * the user (controller) code wants to save or update an object
     * into the database.
     * @param backupitem backup Item
     * @throws edu.byu.isys413.samlam.DataException Data Exception
     */
    public synchronized void save(BackupItem backupitem) throws DataException {
        Connection conn = null;
        try {
            // retrieve a database connection from the pool
            conn = ConnectionPool.getInstance().get();
            // call save with a connection (the other save method in this class)
            this.save(backupitem, conn);
            // release the connection
            conn.commit();
            ConnectionPool.getInstance().release(conn);
        }catch (ConnectionPoolException e4){
            throw new DataException("Could not get a connection to the database.");
        }catch (SQLException e) {
            // rollback
            try {
                conn.rollback();
                ConnectionPool.getInstance().release(conn);
            }catch (ConnectionPoolException e4){
                throw new DataException("Could not get a connection to the database.");
            }catch (SQLException e2) {
                throw new DataException("Big error: could not even release the connection", e2);
            }
            e.printStackTrace();
            throw new DataException("Could not retrieve record for id=" + backupitem.getId(), e);
        }
    }
    
    
    /**
     *  This is a package method that is called by the public save (above) or
     *  by another DAO.  Either way we already have a connection to the database
     *  to use.  The user (controller) code never calls this one directly, since
     *  it can't know about Connection objects or SQLExceptions.
     *
     *  By having other DAOs call this save method (rather than update or
     *  insert below, each DAO in a chained save (like the BackupItemDAO calling
     *  MembershipDAO calling InterestDAO save chain) can independently decide
     *  whether to udpate or insert the BO it's saving.  That's why I made
     *  update and insert private rather than package level -- if you call
     *  them directly from another DAO, this DAO can't decide whether it's
     *  object needs to be inserted or updated.
     */
    synchronized void save(BackupItem backupitem, Connection conn) throws SQLException, DataException {
        // check the dirty flag in the object.  if it is dirty,
        // run update or insert
        if (backupitem.isDirty()) {
            
            if (backupitem.isObjectAlreadyInDB() == true) {
                update(backupitem, conn);
            }else{
                insert(backupitem, conn);
            }
            // set the dirty flag to false now that we've saved it
            backupitem.setDirty(false);
            
        }
        
        // touch the cache for the object
        Cache.getInstance().touch(backupitem.getId());
    }
    
    /**
     * This method is really a part of save(bo, conn) above.  It could be
     * embedded directly in it, but I've separated it into it's own method
     * to isolate the SQL udpate statement and make it more readable.  But
     * logically, it's really just a part of save.
     */
    private synchronized void update(BackupItem backupitem, Connection conn) throws SQLException, DataException {
        // do the update statement
        Statement stmt = conn.createStatement();
        int deleted = 0;
        if(backupitem.isDeleted()){
            deleted = 1;
        }
        stmt.executeUpdate("UPDATE storageitem set storageitem_id='"+backupitem.getId()+"', filename='"+backupitem.getName()+"', extension='"+backupitem.getExtension()+"', filesize='"+backupitem.getFilesize()+"', fileData='"+backupitem.getFileData()+"', uploadDate='"+backupitem.getUploadDate()+"', status='"+backupitem.getStatus()+"', deleted="+deleted+", memberid='"+backupitem.getMemberid()+ "WHERE backupitem_id='"+backupitem.getId()+"'");
        stmt.close();
    }
    
    /**
     * This method is really a part of save(bo, conn) above.  It could be
     * embedded directly in it, but I've separated it into it's own method
     * to isolate the SQL insert statement and make it more readable.  But
     * logically, it's really just a part of save.
     */
    private synchronized void insert(BackupItem backupitem, Connection conn) throws SQLException, DataException {
        // do the insert SQL statement
        PreparedStatement stmt = conn.prepareStatement("INSERT INTO storageitem VALUES (?,?,?,?,?,?,?,?,?)");
        
        stmt.setString(1, backupitem.getId());
        
         stmt.setString(1,backupitem.getId());
         stmt.setString(2, backupitem.getName());
         stmt.setString(3, ((Extension)backupitem.getExtension()).getFileType()); 
         stmt.setLong(4,backupitem.getFilesize());
         stmt.setBinaryStream(5, backupitem.getFileData(),(int)backupitem.getFilesize());
         stmt.setString(6, backupitem.getUploadDate());
         stmt.setString(7, backupitem.getStatus());
         
         if(backupitem.isDeleted()){
             stmt.setInt(8, 1);
         }else{
             stmt.setInt(8, 0);
         }
         stmt.setString(9, backupitem.getMemberid());
        
        stmt.execute();
        // tell the object that it's now in the db (so we call update next time not insert)
        backupitem.setObjectAlreadyInDB(true);
    }
    
    
    ////////////////////////////////////
    ///   DELETE
    // we are not supporting because we never want to delete the backupitem from
    // our database.
    
    
    
    //////////////////////////////
    ///  SEARCH methods
    
    /**
     * get all
     * @param id id
     * @throws edu.byu.isys413.samlam.DataException Data Exception
     * @return Backup Item DAO
     */
    public List<String[]> getAll(String id) throws DataException {
        LinkedList<String[]> allList = new LinkedList();
        // get the connection
        
        Connection conn = null;
        try{
            
            conn = ConnectionPool.getInstance().get();
            
            // sql the names, phone, and ids
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT id, filename, extension, status FROM storageitem where memberid ='" + id + "' AND status='backup'");
            
            // while loop to populate the list from the results
            while(rs.next()) {
                String[] jimmy = {rs.getString("id"), rs.getString("filename"), rs.getString("extension")};
                allList.add(jimmy);
            }
            stmt.close();
            // release my connection
            ConnectionPool.getInstance().release(conn);
            // return the list
            return allList;
        }catch (ConnectionPoolException e4){
            throw new DataException("Could not get a connection to the database.");
        }catch (SQLException e) {
            // rollback
            try {
                conn.rollback();
                ConnectionPool.getInstance().release(conn);
            }catch (ConnectionPoolException e4){
                throw new DataException("Could not get a connection to the database.");
            }catch (SQLException e2) {
                throw new DataException("Big error: could not even release the connection", e2);
            }
            e.printStackTrace();
            throw new DataException("Could not retrieve any backupitem records", e);
        }
        
    }
    
    /**
     * get all
     * @param id id
     * @throws edu.byu.isys413.samlam.DataException Data Exception
     * @return Backup Item DAO
     */
    public List<String[]> getActivePrints(String id) throws DataException {
        LinkedList<String[]> allList = new LinkedList();
        // get the connection
        
        Connection conn = null;
        try{
            
            conn = ConnectionPool.getInstance().get();
            
            // sql the names, phone, and ids
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT id, filename, extension, filesize, uploaddate FROM storageitem where memberid ='" + id + "' AND status='print'");
            
            // while loop to populate the list from the results
            while(rs.next()) {
                String[] jimmy = {rs.getString("id"), rs.getString("filename"), rs.getString("extension"), String.valueOf(rs.getLong("filesize")), rs.getString("uploaddate")};
                allList.add(jimmy);
            }
            stmt.close();
            // release my connection
            ConnectionPool.getInstance().release(conn);
            // return the list
            return allList;
        }catch (ConnectionPoolException e4){
            throw new DataException("Could not get a connection to the database.");
        }catch (SQLException e) {
            // rollback
            try {
                conn.rollback();
                ConnectionPool.getInstance().release(conn);
            }catch (ConnectionPoolException e4){
                throw new DataException("Could not get a connection to the database.");
            }catch (SQLException e2) {
                throw new DataException("Big error: could not even release the connection", e2);
            }
            e.printStackTrace();
            throw new DataException("Could not retrieve any backupitem records", e);
        }
        
    }
}
